/*
	EXEC SP_EXPECTED_DATE_QTY_FINISHED_GOODS 1, 'FAB', 15432, 'LOT_312BAL08.1', 25, '2006-11-03 00:00:00.000', 42, 1
*/
ALTER PROC SP_EXPECTED_DATE_QTY_FINISHED_GOODS( @PROCESSSTEP_ID INT, @PROCESS_TYPE_NAME VARCHAR(50), @LOT_ID INT, @LOT_NUMBER VARCHAR(50), @QTY_IN INT, @DATE_IN DATETIME, @LOCATION_ID INT, @IS_INTRANSIT INT )
AS
BEGIN
	SET NOCOUNT ON
	--DECLARE @PATHS_TABLE TABLE(ROW_ID INT, PATH_NUMBER INT, PROCESSSTEP_ID INT, PRODUCT_ID INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50) )
	DECLARE @EXPECTED_DATE_OUT_TABLE TABLE ( PATH_NUMBER INT, LOT_ID INT, FINAL_PROCESSSTEP_ID INT, FINAL_PRODUCT_ID INT, FINAL_PS_TYPE_NAME VARCHAR(50), QTY_OUT INT, EXP_DATE_OUT DATETIME, BACKLOG_QTY INT )
	
	/*INSERT INTO @PATHS_TABLE 
		SELECT ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PRODUCT_ID, PS_TYPE, PS_TYPE_NAME FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSSTEP_ID,1) ORDER BY PATH_NUMBER, ROW_ID*/
	DECLARE @PATH_NUMBER INT
	DECLARE @PREV_PATH_NUMBER INT
	DECLARE @ROW_ID INT
	DECLARE @EXP_DATE_OUT DATETIME
	DECLARE @STD_CYCLE_TIME FLOAT
	DECLARE @NEXT_PROCESSSTEP_ID INT
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @CURRENT_DATE DATETIME
	DECLARE @TRANSIT_TIME INT
	DECLARE @GROSS_DIE_WAFER FLOAT
	DECLARE @CURRENT_YEAR INT
	DECLARE @CURRENT_QTR INT
	DECLARE @QTY_OUT INT
	DECLARE @YIELD NUMERIC( 5,2 )
	DECLARE @PRODUCT_ID INT
	DECLARE @BACKLOG_QTY INT
	DECLARE @LOT_VEND_EXP_DATE_OUT DATETIME

	SET @CURRENT_YEAR = YEAR( GETDATE() )
	SET @CURRENT_QTR  = DATEPART( Q, GETDATE() )
	
	SET @CURRENT_DATE = GETDATE()
	IF ( (SELECT COUNT(*) FROM WIP_Flush_Table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID) = 0 )
	 BEGIN
		EXEC SP_CREATE_WIP_FLUSH_SETUP @PROCESSSTEP_ID
	 END
	
	IF ( @PROCESS_TYPE_NAME = 'FAB' )
	 BEGIN
		SELECT @LOT_VEND_EXP_DATE_OUT = FL_EXPECTED_DATE_OUT FROM FAB_LOT WHERE LOT_ID = @LOT_ID
	 END
	ELSE IF ( @PROCESS_TYPE_NAME = 'Sort' )
	 BEGIN
		SELECT @LOT_VEND_EXP_DATE_OUT = SL_EXPECTED_DATE_OUT FROM SORT_LOT WHERE LOT_ID = @LOT_ID
	 END	
	ELSE IF ( @PROCESS_TYPE_NAME = 'Wafer Store' OR @PROCESS_TYPE_NAME = 'Die Bank' OR @PROCESS_TYPE_NAME = 'Class Store' OR @PROCESS_TYPE_NAME = 'Mark & Pack' OR @PROCESS_TYPE_NAME = 'Finished Goods' )
	 BEGIN
		SELECT @LOT_VEND_EXP_DATE_OUT = EXPECTED_DATE_OUT FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
	 END	
	ELSE IF ( @PROCESS_TYPE_NAME = 'Assembly' )
	 BEGIN
		SELECT @LOT_VEND_EXP_DATE_OUT = EXPECTED_DATE_OUT FROM ASSEMBLY_LOT WHERE LOT_ID = @LOT_ID
	 END	
	ELSE IF ( @PROCESS_TYPE_NAME = 'TEST' )
	 BEGIN
		SELECT @LOT_VEND_EXP_DATE_OUT = FTL_EXPECTED_DATE FROM FINAL_TEST_LOT WHERE LOT_ID = @LOT_ID
	 END

	IF ( @IS_INTRANSIT != 0 )
	 BEGIN
		INSERT INTO  @EXPECTED_DATE_OUT_TABLE( PATH_NUMBER, LOT_ID, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, QTY_OUT , EXP_DATE_OUT, BACKLOG_QTY )
			SELECT 0, @LOT_ID, WFT.FG_PROCESSSTEP_ID, WFT.FG_PRODUCT_ID, ROUND(( @QTY_IN * WFT.GROSS_DIE_FACTOR * WFT.FINAL_YIELD )/100, 0 ), 
				(CASE WHEN @LOT_VEND_EXP_DATE_OUT IS NULL
					THEN
						(CASE WHEN DATEDIFF(DD,@DATE_IN, @CURRENT_DATE)>WFT.CURRENT_PROCESS_CYCLE_TIME 
							THEN 
								(@DATE_IN + WFT.TOTAL_CYCLE_TIME+(DATEDIFF(DD,@DATE_IN, @CURRENT_DATE)-WFT.CURRENT_PROCESS_CYCLE_TIME))
							ELSE
								(@DATE_IN + WFT.TOTAL_CYCLE_TIME)
							END
						)
					ELSE
						(CASE WHEN DATEDIFF(DD,@CURRENT_DATE, @LOT_VEND_EXP_DATE_OUT) > 0 
						    	THEN
								(@LOT_VEND_EXP_DATE_OUT + WFT.TOTAL_CYCLE_TIME - WFT.CURRENT_PROCESS_CYCLE_TIME)
							ELSE
								(@CURRENT_DATE + WFT.TOTAL_CYCLE_TIME - WFT.CURRENT_PROCESS_CYCLE_TIME)
						 	END
						)
				END
				) AS EXP_DATE_OUT,
				(SELECT ISNULL(SUM(COOL_QTY) - ISNULL((SELECT SUM(CSLIT.CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL 
				WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = WFT.FG_PRODUCT_ID AND CSLIT.CSLIT_STATUS = 'SHIPPED'),0), 0 )
				FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE PRODUCT_ID = WFT.FG_PRODUCT_ID) AS BACKLOG_QTY
				--(SELECT ISNULL(SUM((COOL.COOL_QTY-COOL.COOL_SHIPPED_QTY)),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE COOL.COOL_STATUS <> 'SHIPPED' AND COOL.PRODUCT_ID = WFT.FG_PRODUCT_ID)
			 FROM WIP_Flush_Table WFT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	 END
	ELSE --TRANSIT LOT
	 BEGIN
		INSERT INTO  @EXPECTED_DATE_OUT_TABLE( PATH_NUMBER, LOT_ID, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, QTY_OUT , EXP_DATE_OUT, BACKLOG_QTY )
			SELECT 0, @LOT_ID, WFT.FG_PROCESSSTEP_ID, WFT.FG_PRODUCT_ID, ROUND(( @QTY_IN * WFT.GROSS_DIE_FACTOR * WFT.FINAL_YIELD )/100, 0 ), 
				(CASE WHEN DATEDIFF(DD,@DATE_IN, @CURRENT_DATE)>WFT.CURRENT_PROCESS_TRANSIT_TIME 
					THEN 
						(@DATE_IN + WFT.TOTAL_CYCLE_TIME+(DATEDIFF(DD,@DATE_IN, @CURRENT_DATE)-WFT.CURRENT_PROCESS_TRANSIT_TIME) - WFT.CURRENT_PROCESS_CYCLE_TIME)
					ELSE
						(@DATE_IN + WFT.TOTAL_CYCLE_TIME - WFT.CURRENT_PROCESS_CYCLE_TIME)
					END
				) AS EXP_DATE_OUT,
				(SELECT ISNULL(SUM(COOL_QTY) - ISNULL((SELECT SUM(CSLIT.CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL 
				WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = WFT.FG_PRODUCT_ID AND CSLIT.CSLIT_STATUS = 'SHIPPED'),0), 0 )
				FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE PRODUCT_ID = WFT.FG_PRODUCT_ID) AS BACKLOG_QTY
				--(SELECT ISNULL(SUM((COOL.COOL_QTY-COOL.COOL_SHIPPED_QTY)),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE COOL.COOL_STATUS <> 'SHIPPED' AND COOL.PRODUCT_ID = WFT.FG_PRODUCT_ID)
			 FROM WIP_Flush_Table WFT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	 END

	/*
	SELECT TOP 1 @ROW_ID = ROW_ID, @PATH_NUMBER = PATH_NUMBER, @NEXT_PROCESSSTEP_ID = PROCESSSTEP_ID, @PRODUCT_ID = PRODUCT_ID, @PS_TYPE_NAME = PS_TYPE_NAME FROM @PATHS_TABLE
	INSERT INTO  @EXPECTED_DATE_OUT_TABLE VALUES ( @PATH_NUMBER, @LOT_ID, @PROCESSSTEP_ID, @PRODUCT_ID, @PROCESS_TYPE_NAME, @QTY_IN, @DATE_IN, 0 )
	SET @PREV_PATH_NUMBER = @PATH_NUMBER
	WHILE ( @ROW_ID > 0 )
	 BEGIN
		SET @EXP_DATE_OUT = @CURRENT_DATE
		SET @QTY_OUT = 0
		SET @TRANSIT_TIME = 0
		SET @YIELD = 100.00
		IF ( @PATH_NUMBER != @PREV_PATH_NUMBER )
		 BEGIN
			INSERT INTO  @EXPECTED_DATE_OUT_TABLE VALUES ( @PATH_NUMBER, @LOT_ID, @NEXT_PROCESSSTEP_ID, @PRODUCT_ID, @PS_TYPE_NAME, @QTY_IN, @DATE_IN, 0 )		
			SET @PREV_PATH_NUMBER = @PATH_NUMBER
		 END

		SELECT @EXP_DATE_OUT = EXP_DATE_OUT, @QTY_OUT = QTY_OUT FROM @EXPECTED_DATE_OUT_TABLE WHERE PATH_NUMBER = @PATH_NUMBER
		IF ( @IS_INTRANSIT != 0 )
		 BEGIN
			SELECT @STD_CYCLE_TIME = ISNULL(PARAMETER_VALUE,0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
			SET @EXP_DATE_OUT = @EXP_DATE_OUT + ISNULL(@STD_CYCLE_TIME,0)
			IF @EXP_DATE_OUT < @CURRENT_DATE
			BEGIN
				SET @EXP_DATE_OUT = @CURRENT_DATE
			END
			SELECT @TRANSIT_TIME = ISNULL( TT_NORMAL_TIME, 0 ) FROM TRANSPORTATION_TABLE WHERE PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
			SET @EXP_DATE_OUT = DATEADD( day, ISNULL(@TRANSIT_TIME,0), @EXP_DATE_OUT )
		 END
		ELSE --TRANSIT LOT
		 BEGIN
			SELECT @TRANSIT_TIME = ISNULL( TT_NORMAL_TIME, 0 ) FROM TRANSPORTATION_TABLE WHERE PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
			SET @EXP_DATE_OUT = @EXP_DATE_OUT + @TRANSIT_TIME
			IF @EXP_DATE_OUT < @CURRENT_DATE
			BEGIN
				SET @EXP_DATE_OUT = @CURRENT_DATE
			END
		 END

		SELECT @YIELD = CASE WHEN @CURRENT_QTR = 1 THEN ISNULL ( Q1_YIELD, 100 ) WHEN @CURRENT_QTR = 2 THEN ISNULL ( Q2_YIELD, 100) WHEN @CURRENT_QTR = 3 THEN ISNULL ( Q3_YIELD, 100) WHEN @CURRENT_QTR = 4 THEN ISNULL ( Q4_YIELD, 100) END 
		FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID AND YEAR ( FIN_YEAR ) = @CURRENT_YEAR
		SET @YIELD = ( CASE WHEN @YIELD IS NULL OR @YIELD = 0.0 THEN 100.00 ELSE @YIELD END )
		SET @QTY_OUT = ROUND(( @QTY_OUT * @YIELD )/100, 0 )
		SET @BACKLOG_QTY = 0
		SELECT @BACKLOG_QTY = ISNULL(SUM((COOL.COOL_QTY-COOL.COOL_SHIPPED_QTY)),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE COOL.COOL_STATUS <> 'SHIPPED' AND COOL.PRODUCT_ID = @PRODUCT_ID
		UPDATE @EXPECTED_DATE_OUT_TABLE SET EXP_DATE_OUT = @EXP_DATE_OUT, QTY_OUT = @QTY_OUT, FINAL_PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID, FINAL_PRODUCT_ID = @PRODUCT_ID, FINAL_PS_TYPE_NAME = @PS_TYPE_NAME, BACKLOG_QTY = @BACKLOG_QTY WHERE PATH_NUMBER = @PATH_NUMBER
		DELETE FROM @PATHS_TABLE WHERE ROW_ID = @ROW_ID

		SET @ROW_ID = 0
		SET @PATH_NUMBER = 0
		SET @NEXT_PROCESSSTEP_ID = NULL
		SET @PS_TYPE_NAME = NULL
		SELECT TOP 1 @ROW_ID = ROW_ID, @PATH_NUMBER = PATH_NUMBER, @NEXT_PROCESSSTEP_ID = PROCESSSTEP_ID, @PRODUCT_ID = PRODUCT_ID, @PS_TYPE_NAME = PS_TYPE_NAME FROM @PATHS_TABLE		 
	 END
	
	DELETE FROM @EXPECTED_DATE_OUT_TABLE WHERE FINAL_PS_TYPE_NAME != 'FINISHED GOODS'
	SET @NEXT_PROCESSSTEP_ID = NULL
	SET @QTY_OUT = 0
	SET @EXP_DATE_OUT = NULL*/

	/*SELECT TOP 1 @NEXT_PROCESSSTEP_ID = FINAL_PROCESSSTEP_ID, @QTY_OUT = QTY_OUT, @EXP_DATE_OUT = EXP_DATE_OUT FROM @EXPECTED_DATE_OUT_TABLE ORDER BY BACKLOG_QTY DESC, EXP_DATE_OUT ASC
	IF ( @NEXT_PROCESSSTEP_ID IS NOT NULL )
	 BEGIN
		IF @EXP_DATE_OUT < @CURRENT_DATE
		BEGIN
			SET @EXP_DATE_OUT = @CURRENT_DATE
		END
		INSERT INTO [WIP_MASTER]( [LOT_ID], [LOT_NUMBER], [PROCESSSTEP_ID], [LOCATION_ID], [QTY_IN], [DATE_IN], [FG_PROCESSSTEP_ID], [FG_QTY], [FG_DATE] )
	       	VALUES( @LOT_ID, @LOT_NUMBER, @PROCESSSTEP_ID, @LOCATION_ID, @QTY_IN, @DATE_IN, @NEXT_PROCESSSTEP_ID, @QTY_OUT, @EXP_DATE_OUT )
	 END*/
	DECLARE @EXP_DATE_OUT_CURSOR CURSOR
	DECLARE @IS_CONSIDER_FOR_REPORT INT
	SET @IS_CONSIDER_FOR_REPORT = 0 --CONSIDER IN SHIPPABLE REPORT
	SET @EXP_DATE_OUT_CURSOR = CURSOR FOR SELECT FINAL_PROCESSSTEP_ID, QTY_OUT, EXP_DATE_OUT FROM @EXPECTED_DATE_OUT_TABLE ORDER BY BACKLOG_QTY DESC, EXP_DATE_OUT ASC
	OPEN @EXP_DATE_OUT_CURSOR
	FETCH NEXT FROM @EXP_DATE_OUT_CURSOR INTO @NEXT_PROCESSSTEP_ID, @QTY_OUT, @EXP_DATE_OUT
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		IF ( @NEXT_PROCESSSTEP_ID IS NOT NULL )
		 BEGIN
			IF @EXP_DATE_OUT < @CURRENT_DATE
			BEGIN
				SET @EXP_DATE_OUT = @CURRENT_DATE
			END
			INSERT INTO [WIP_MASTER]( [LOT_ID], [LOT_NUMBER], [PROCESSSTEP_ID], [LOCATION_ID], [QTY_IN], [DATE_IN], [FG_PROCESSSTEP_ID], [FG_QTY], [FG_DATE], [QTY_ALLOCATED], [IS_CONSIDER_FOR_REPORT] )
		       		VALUES( @LOT_ID, @LOT_NUMBER, @PROCESSSTEP_ID, @LOCATION_ID, @QTY_IN, @DATE_IN, @NEXT_PROCESSSTEP_ID, @QTY_OUT, @EXP_DATE_OUT, 0, @IS_CONSIDER_FOR_REPORT )
			SET @IS_CONSIDER_FOR_REPORT = 1 --DO NOT CONSIDER IN SHIPPABLE REPORT
		 END
		SET @NEXT_PROCESSSTEP_ID = NULL
		SET @QTY_OUT = 0
		SET @EXP_DATE_OUT = NULL
		FETCH NEXT FROM @EXP_DATE_OUT_CURSOR INTO @NEXT_PROCESSSTEP_ID, @QTY_OUT, @EXP_DATE_OUT
	 END
	CLOSE @EXP_DATE_OUT_CURSOR
	DEALLOCATE @EXP_DATE_OUT_CURSOR
END